#!/usr/bin/env python
# -*- coding: utf-8 -*-


class ModelBase(object):
    '数据库模型基类，定义了连接和关闭操作'
    def __init__(self):
        import MySQLdb
        from util import config

        self._conn = MySQLdb.connect(
                host = config.CONFIG['db_host'],
                db = config.CONFIG['db_name'],
                user = config.CONFIG['db_user'],
                passwd = config.CONFIG['db_passwd'],
                charset = config.CONFIG['db_charset'])
        cursor_class = MySQLdb.cursors.DictCursor
        self._cursor = self._conn.cursor(cursorclass = cursor_class)

    def __del__(self):
        self._cursor.close()
        self._conn.close()


class ModelStaffInfo(ModelBase):
    '员工表数据模型'
    def get_all_with_leader(self):
        '查询所有员工信息（包含组长信息）'
        sql_str = '''
        select
            staff_info.name,
            staff_info.gender,
            staff_info.popo,
            belong_info.group_name,
            group_info_a.leader_name,
            group_info_a.leader_popo
            from staff_info, belong_info
            left join
            (
            select
                group_info.group_name,
                group_info.leader leader_popo,
                staff_info.name leader_name
                from group_info, staff_info
                where group_info.leader=staff_info.popo
                ) group_info_a
            on belong_info.group_name=group_info_a.group_name
            where belong_info.popo=staff_info.popo;
        '''
        count = self._cursor.execute(sql_str)
        record = self._cursor.fetchall()
        return record

    def get_by_query_with_leader(self, query):
        '根据条件查询员工信息（包含组长信息）'
        if not query:
            return {}
        sql_str = '''
        select
            staff_info.name,
            staff_info.gender,
            staff_info.popo,
            belong_info.group_name,
            group_info_a.leader_name,
            group_info_a.leader_popo
            from staff_info, belong_info
            left join
            (
            select
                group_info.group_name,
                group_info.leader leader_popo,
                staff_info.name leader_name
                from group_info, staff_info
                where group_info.leader=staff_info.popo
                ) group_info_a
            on belong_info.group_name=group_info_a.group_name
            where belong_info.popo=staff_info.popo and
        '''
        sql_str += ' and '.join(['staff_info.' + k + '=%s' for k in query])
        query_list = [query[k] for k in query]
        count = self._cursor.execute(sql_str, query_list)
        record = self._cursor.fetchall()
        return record


class ModelGroupInfo(ModelBase):
    '组表数据模型'
    def get_all_with_count(self):
        '获得所有组信息（包含每组员工数）'
        sql_str = '''
        select
        belong_info.group_name 'group_name',
        group_info_a.leader_name 'leader_name',
        count(staff_info.popo) 'count'
        from staff_info
        left join belong_info
        on belong_info.popo=staff_info.popo
            left join
            (
            select
            group_info.group_name,
            staff_info.name leader_name
            from group_info
            left join staff_info
            on group_info.leader=staff_info.popo
            ) group_info_a
            on group_info_a.group_name=belong_info.group_name
            group by belong_info.group_name
            with rollup;
        '''
        count = self._cursor.execute(sql_str)
        record = self._cursor.fetchall()
        return record

    def get_mm_ratio(self):
        '查询mm比例'
        sql_str = '''
        select
            group_name 'group_name',
            count(case when gender='F' then 0 else null end)/count(*) 'ratio'
            from staff_info
            join belong_info on belong_info.popo=staff_info.popo
            group by belong_info.group_name
            with rollup;
        '''
        count = self._cursor.execute(sql_str)
        record = self._cursor.fetchall()

        return record
